package com.example.webdemo.dao.Impl;

import com.example.webdemo.bean.customer;
import com.example.webdemo.dao.CustomerDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class CustomerDaoImpl implements CustomerDao {

    private final JdbcTemplate jdbcTemplate;

    // 缓存字典数据
    private Map<String, Map<String, String>> dictCache = new HashMap<>();

    // RowMapper 用于将ResultSet映射到Customer对象
    private final RowMapper<customer> customerRowMapper = (rs, rowNum) -> {
        customer customer = new customer();
        customer.setCust_id(rs.getInt("cust_id"));
        customer.setCust_name(rs.getString("cust_name"));

        // 从字典缓存中获取对应的显示名称
        customer.setCust_type(getDictName("customer_type", rs.getString("cust_type")));
        customer.setCust_source(getDictName("customer_source", rs.getString("cust_source")));
        customer.setCust_industry(getDictName("customer_industry", rs.getString("cust_industry")));
        customer.setCust_level(getDictName("customer_level", rs.getString("cust_level")));

        customer.setCust_linkman(rs.getString("cust_linkman"));
        customer.setCust_phone(rs.getString("cust_phone"));
        customer.setCust_mobile(rs.getString("cust_mobile"));
        customer.setCust_email(rs.getString("cust_email"));
        customer.setCust_address(rs.getString("cust_address"));
        customer.setCust_remark(rs.getString("cust_remark"));
        customer.setUser_id(rs.getInt("user_id"));
        customer.setCreate_time(rs.getDate("create_time"));
        customer.setUpdate_time(rs.getDate("update_time"));

        // 设置原始代码值
//        customer.setOriginalCustType(rs.getString("cust_type"));
//        customer.setOriginalCustSource(rs.getString("cust_source"));
//        customer.setOriginalCustIndustry(rs.getString("cust_industry"));
//        customer.setOriginalCustLevel(rs.getString("cust_level"));

        return customer;
    };

    @Autowired
    public CustomerDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        loadDictCache();
    }

    // 加载字典数据到缓存
    private void loadDictCache() {
        String sql = "SELECT dict_type, dict_code, dict_name FROM base_dict WHERE status = 1 ORDER BY sort";
        jdbcTemplate.query(sql, (rs, rowNum) -> {
            String dictType = rs.getString("dict_type");
            String dictCode = rs.getString("dict_code");
            String dictName = rs.getString("dict_name");

            dictCache.computeIfAbsent(dictType, k -> new HashMap<>())
                    .put(dictCode, dictName);
            return null;
        });
    }

    // 根据字典类型和代码获取名称
    private String getDictName(String dictType, String dictCode) {
        if (dictCode == null) return null;
        Map<String, String> typeMap = dictCache.get(dictType);
        return typeMap != null ? typeMap.getOrDefault(dictCode, dictCode) : dictCode;
    }

    // 根据字典类型和名称获取代码
    private String getDictCode(String dictType, String dictName) {
        if (dictName == null) return null;
        Map<String, String> typeMap = dictCache.get(dictType);
        if (typeMap != null) {
            for (Map.Entry<String, String> entry : typeMap.entrySet()) {
                if (entry.getValue().equals(dictName)) {
                    return entry.getKey();
                }
            }
        }
        return dictName;
    }

    @Override
    public int insert(customer customer) {
        String sql = "INSERT INTO customer (" +
                "cust_name, cust_type, cust_source, cust_industry, cust_level, " +
                "cust_linkman, cust_phone, cust_mobile, cust_email, cust_address, " +
                "cust_remark, user_id) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        return jdbcTemplate.update(sql,
                customer.getCust_name(),
                getDictCode("customer_type", customer.getCust_type()),
                getDictCode("customer_source", customer.getCust_source()),
                getDictCode("customer_industry", customer.getCust_industry()),
                getDictCode("customer_level", customer.getCust_level()),
                customer.getCust_linkman(),
                customer.getCust_phone(),
                customer.getCust_mobile(),
                customer.getCust_email(),
                customer.getCust_address(),
                customer.getCust_remark(),
                customer.getUser_id());
    }

    @Override
    public int insertAndReturnId(customer customer) {
        String sql = "INSERT INTO customer (" +
                "cust_name, cust_type, cust_source, cust_industry, cust_level, " +
                "cust_linkman, cust_phone, cust_mobile, cust_email, cust_address, " +
                "cust_remark, user_id) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, customer.getCust_name());
            ps.setString(2, getDictCode("customer_type", customer.getCust_type()));
            ps.setString(3, getDictCode("customer_source", customer.getCust_source()));
            ps.setString(4, getDictCode("customer_industry", customer.getCust_industry()));
            ps.setString(5, getDictCode("customer_level", customer.getCust_level()));
            ps.setString(6, customer.getCust_linkman());
            ps.setString(7, customer.getCust_phone());
            ps.setString(8, customer.getCust_mobile());
            ps.setString(9, customer.getCust_email());
            ps.setString(10, customer.getCust_address());
            ps.setString(11, customer.getCust_remark());
            ps.setInt(12, customer.getUser_id());
            return ps;
        }, keyHolder);

        return keyHolder.getKey().intValue();
    }

    @Override
    public int update(customer customer) {
        String sql = "UPDATE customer SET " +
                "cust_name = ?, cust_type = ?, cust_source = ?, cust_industry = ?, " +
                "cust_level = ?, cust_linkman = ?, cust_phone = ?, cust_mobile = ?, " +
                "cust_email = ?, cust_address = ?, cust_remark = ?, user_id = ? " +
                "WHERE cust_id = ?";

        return jdbcTemplate.update(sql,
                customer.getCust_name(),
                getDictCode("customer_type", customer.getCust_type()),
                getDictCode("customer_source", customer.getCust_source()),
                getDictCode("customer_industry", customer.getCust_industry()),
                getDictCode("customer_level", customer.getCust_level()),
                customer.getCust_linkman(),
                customer.getCust_phone(),
                customer.getCust_mobile(),
                customer.getCust_email(),
                customer.getCust_address(),
                customer.getCust_remark(),
                customer.getUser_id(),
                customer.getCust_id());
    }

    @Override
    public int delete(int custId) {
        String sql = "DELETE FROM customer WHERE cust_id = ?";
        return jdbcTemplate.update(sql, custId);
    }

    @Override
    public customer findById(int custId) {
        String sql = "SELECT * FROM customer WHERE cust_id = ?";
        try {
            return jdbcTemplate.queryForObject(sql, customerRowMapper, custId);
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public List<customer> findAll() {
        String sql = "SELECT * FROM customer ORDER BY cust_id ";
        return jdbcTemplate.query(sql, customerRowMapper);
    }

    @Override
    public List<customer> findByCondition(String custName, String custType, String custSource) {
        StringBuilder sql = new StringBuilder("SELECT * FROM customer WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (custName != null && !custName.isEmpty()) {
            sql.append(" AND cust_name LIKE ?");
            params.add("%" + custName + "%");
        }

        if (custType != null && !custType.isEmpty()) {
            sql.append(" AND cust_type = ?");
            params.add(getDictCode("customer_type", custType));
        }

        if (custSource != null && !custSource.isEmpty()) {
            sql.append(" AND cust_source = ?");
            params.add(getDictCode("customer_source", custSource));
        }

        sql.append(" ORDER BY create_time DESC");

        return jdbcTemplate.query(sql.toString(), customerRowMapper, params.toArray());
    }

    @Override
    public List<customer> findByPage(int pageNum, int pageSize) {
        int offset = (pageNum - 1) * pageSize;
        String sql = "SELECT * FROM customer ORDER BY cust_id DESC LIMIT ?, ?";
        return jdbcTemplate.query(sql, customerRowMapper, offset, pageSize);
    }

    @Override
    public int count() {
        String sql = "SELECT COUNT(*) FROM customer";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public List<customer> findByUserId(int userId) {
        String sql = "SELECT * FROM customer WHERE user_id = ? ORDER BY cust_id ";
        return jdbcTemplate.query(sql, customerRowMapper, userId);
    }

    // 获取字典类型的所有选项
    public Map<String, String> getDictOptions(String dictType) {
        return dictCache.getOrDefault(dictType, new HashMap<>());
    }
}